﻿/*
*VPFConfigurature .cs
*
* 功 能： 本地Visual FoxPro数据库导入到服务器数据库,连接本地数据库和从本地库获得数据
* 类 名： VPFConfigurature
*
* Ver    变更日期  20130803   负责人  黄永生  变更内容
* ───────────────────────────────────

*/
using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System.Xml;
using XMK.Tadong.Data;
using System.IO;


namespace XMK.Tadong.StructuredData_IntoDb.Data
{
  public   class VPFConfigurature:IConfigurature
    {
        private string conStr = null;
        private OleDbConnection connection = null;
        private DataTable dataTable = new DataTable();
        private string[] restrictStr = new string[3];
        private string desTableName;
        private string srcTableName;
        private GridView gridView;
        private string filePath;//= "System.Windows.Forms.Application.StartupPath"+"\\vpf_conf.xml";
        private List<ArrayList> listArrayList;
        protected static DataTable dtError;                   //错误信息表
        private string[] congInfo;
        private string filePath1;

        public VPFConfigurature(string[] confInfo, string FilePath)
        {
            conStr = @"provider=vfpoledb.1;;data source=" + confInfo[0];
            connection = new OleDbConnection(conStr);
            FileInfo file = new FileInfo(confInfo[0]);
            srcTableName = file.Name.Remove(file.Name.LastIndexOf("."));
            desTableName = confInfo[1];

            this.filePath = FilePath;
        }
        #region IConfigurature Members

        public List<string> GetTableScama()
        {
            List<string> tabList = new List<string>();
            tabList.Add(srcTableName);
            return tabList;
        }

        public List<string> GetColumnsScama(string tableName)
        {
            dataTable.Columns.Clear();
            restrictStr[2] = tableName;
            List<string> comlis = new List<string>();
            //comlis.Add("");
            connection.Close();
            connection.Open();
            try
            {
                dataTable = connection.GetSchema("columns", restrictStr);
                DataRowCollection rows = dataTable.Rows;
                foreach (DataRow row in rows)
                {
                    comlis.Add(row[3].ToString());
                }
            }

            finally
            {
                connection.Close();
            }
            return comlis;
        }

        public System.Data.DataTable GetQueryResultSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OleDbCommand command = new OleDbCommand(queryStr, connection);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);

            dtError = new DataTable("ErrorInfo");
            dtError.Columns.Add("errorrow", System.Type.GetType("System.String"));
            dtError.Columns.Add("errorcol", System.Type.GetType("System.String"));
            dtError.Columns.Add("errorinfo", System.Type.GetType("System.String"));
            adapter.FillError += new FillErrorEventHandler(FillError);
            //try
            //{;
            adapter.TableMappings.Add("Table", "data");
            adapter.Fill(ds, "data");
            adapter.FillError -= new FillErrorEventHandler(FillError);
            //}
            //catch (Exception ex)
            //{
            //    //throw ex;
            //}
            return ds.Tables[0];
        }
        /// <summary>
        /// 获得查询结果，
        /// </summary>
        /// <param name="queryStr">查询sql</param>
        /// <returns></returns>
        public System.Data.DataSet GetQueryResultDataSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OleDbCommand command = new OleDbCommand(queryStr, connection);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);

            dtError = new DataTable("ErrorInfo");
            dtError.Columns.Add("errorrow", System.Type.GetType("System.String"));
            dtError.Columns.Add("errorcol", System.Type.GetType("System.String"));
            dtError.Columns.Add("errorinfo", System.Type.GetType("System.String"));
            adapter.FillError += new FillErrorEventHandler(FillError);
            //try
            //{;
            adapter.TableMappings.Add("Table", "data");
            adapter.Fill(ds, "data");
            adapter.FillError -= new FillErrorEventHandler(FillError);
            //}
            //catch (Exception ex)
            //{
            //    //throw ex;
            //}
            ds.Tables.Add(dtError);
            return ds;
        }
        /// <summary>
        /// 在填充数据到dataset中时 出错事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        protected static void FillError(object sender, System.Data.FillErrorEventArgs args)
        {
            //if (args.Errors.GetType() == typeof(System.InvalidOperationException))
            //{
            DataRow dr = args.DataTable.NewRow();
            DataRow drError = dtError.NewRow();
            for (int i = 0; i < args.DataTable.Columns.Count; i++)
            {
                try
                {
                    if (args.Values[i] == null || args.Values[i].ToString() == "")
                    {
                        drError[1] = Convert.ToString(i + 1);
                        drError[2] = args.Errors.Message.ToString();
                        dr[i] = DBNull.Value;
                    }
                    else
                    {
                        dr[i] = args.Values[i];
                    }
                }
                catch
                { }
            }
            args.DataTable.Rows.Add(dr.ItemArray);
            drError[0] = args.DataTable.Rows.Count.ToString();
            dtError.Rows.Add(drError);
            //}
            //if (args.Errors.GetType() == typeof(System.OverflowException))
            //{
            //}
            args.Continue = true;
        }
        //保存配置文件
        public void SaveConf(GridView d)
        {
            this.gridView = d;
            FileInfo confFile = new FileInfo(filePath);
            if (!confFile.Exists)
            {
                CreateVpfConfFile(filePath);
            }
            else
            {
                ModifyVpfConfFile(filePath);
            }
            JsAlert.Alert("配置文件已保存！");
        }
        /// <summary>
        /// 保存配置信息
        /// </summary>
        /// <param name="laList">字段对应数据</param>
        public void SaveConf(List<ArrayList> laList)
        {
            this.listArrayList = laList;
            FileInfo confFile = new FileInfo(filePath);
            if (confFile.Exists)
            {
                confFile.Delete();
            }
            CreateVpfConfFile_List(filePath);
            JsAlert.Alert("配置文件已保存！");
        }


        public void LoadConf(DropDownList tableSelectBox, GridView dgv)
        {
            this.gridView = dgv;
            FileInfo confFile = new FileInfo(filePath);
            if (!confFile.Exists)
            {
                JsAlert.Alert("配置文件不存在");
            }
            else
            {
                bool nodeContained = false;
                XmlDocument doc = new XmlDocument();
                doc.Load(filePath);
                XmlNodeList tableList = doc.GetElementsByTagName("Table");

                foreach (XmlNode node in tableList)
                {
                    if (node.Attributes["des_table"].Value.ToString() == desTableName)
                    {
                        nodeContained = true;
                        tableSelectBox.SelectedIndex = 0;
                        LoadFieldMapping(node);
                        break;
                    }
                }

                if (!nodeContained)
                {
                    JsAlert.Alert("配置文件中没有保存该表的字段映射，请重新配置后保存");
                }
            }
        }

        #endregion

        //SAX方式写入XML
        private void CreateVpfConfFile(string fileName)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            //settings.NewLineOnAttributes = true;
            XmlWriter writer = XmlWriter.Create(fileName, settings);
            writer.WriteStartDocument(); //xml声明

            writer.WriteStartElement("VPF-Conf");//根节点开始

            //数据库映射配置 
            writer.WriteStartElement("Tables");//tables节点开始
            writer.WriteStartElement("Table");//table节点开始
            writer.WriteAttributeString("des_table", desTableName);

            //设置表字段映射
            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                writer.WriteStartElement("Field");//字段节点开始
                //writer.WriteAttributeString("des_field", this.gridView[2, i].Value.ToString());
                //value = this.gridView[colNumber, i].Value;
                writer.WriteAttributeString("des_field", this.gridView.Rows[i].Cells[2].Text.ToString());
                value = this.gridView.Rows[i].Cells[colNumber].Text.ToString();


                if (value == null)
                {
                    writer.WriteAttributeString("src_field", "");
                }
                else
                {
                    writer.WriteAttributeString("src_field", value.ToString());
                }
                writer.WriteEndElement();//字段节点结束
            }

            writer.WriteEndElement();//table节点结束
            writer.WriteEndElement();//tables节点结束
            writer.WriteEndElement();//根节点结束
            writer.WriteEndDocument();

            writer.Flush();
            writer.Close();

        }

        /// <summary>
        /// 新建Vpf数据库连接配置文件采用list数据
        /// </summary>
        /// <param name="fileName">配置文件名</param>
        private void CreateVpfConfFile_List(string fileName)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            //settings.NewLineOnAttributes = true;
            XmlWriter writer = XmlWriter.Create(fileName, settings);
            writer.WriteStartDocument(); //xml声明

            writer.WriteStartElement("VPF-Conf");//根节点开始

            //数据库映射配置 
            writer.WriteStartElement("Tables");//tables节点开始
            writer.WriteStartElement("Table");//table节点开始
            writer.WriteAttributeString("des_table", desTableName);

            //设置表字段映射
            int rowCount = this.listArrayList.Count;

            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                writer.WriteStartElement("Field");//字段节点开始
                writer.WriteAttributeString("des_field", this.listArrayList[i][0].ToString());
                value = this.listArrayList[i][1].ToString();


                if (value == null)
                {
                    writer.WriteAttributeString("src_field", "");
                }
                else
                {
                    writer.WriteAttributeString("src_field", value.ToString());
                }
                writer.WriteEndElement();//字段节点结束
            }

            writer.WriteEndElement();//table节点结束
            writer.WriteEndElement();//tables节点结束
            writer.WriteEndElement();//根节点结束
            writer.WriteEndDocument();

            writer.Flush();
            writer.Close();

        }
        //DOM方式检索，保存XML
        private void ModifyVpfConfFile(string fileName)
        {
            bool nodeContained = false;
            XmlDocument doc = new XmlDocument();
            doc.Load(fileName);

            XmlNodeList tableList = doc.GetElementsByTagName("Table");

            foreach (XmlNode node in tableList)
            {
                if (node.Attributes["des_table"].Value.ToString() == desTableName)
                {
                    node.RemoveAll();
                    AddFieldMapingNode(doc, node);
                    nodeContained = true;
                    break;
                }
            }

            if (!nodeContained)
            {
                XmlNode tableNode = doc.CreateNode("element", "Table", null);
                XmlNodeList ts = doc.GetElementsByTagName("Tables");
                if (ts.Count > 0)
                {
                    ts[0].AppendChild(tableNode);
                }
                AddFieldMapingNode(doc, tableNode);
                nodeContained = false;
            }

            doc.Save(fileName);
        }

        //为表添加字段映射节点
        private void AddFieldMapingNode(XmlDocument doc, XmlNode node)
        {
            node.Attributes.Append(doc.CreateAttribute("des_table"));
            node.Attributes["des_table"].Value = desTableName;

            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                XmlElement element = doc.CreateElement("Field");
                //element.SetAttribute("des_field", this.gridView[2, i].Value.ToString());
                //value = this.gridView[colNumber, i].Value;
                element.SetAttribute("des_field", this.gridView.Rows[i].Cells[2].Text.ToString());
                value = this.gridView.Rows[i].Cells[colNumber].Text.ToString();

                if (value == null)
                {
                    element.SetAttribute("src_field", "");
                }
                else
                {
                    element.SetAttribute("src_field", value.ToString());
                }
                node.AppendChild(element);
            }
        }

        private void LoadFieldMapping(XmlNode tableNode)
        {
            XmlNodeList tableList = tableNode.ChildNodes;
            Hashtable ht = new Hashtable();
            foreach (XmlNode node in tableList)
            {
                ht.Add(node.Attributes["des_field"].Value.ToString(), node.Attributes["src_field"].Value.ToString());
            }
            List<string> ls = GetColumnsScama(srcTableName);
            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                //value = ht[this.gridView[2, i].Value.ToString()];
                value = ht[this.gridView.Rows[i].Cells[1].Text.ToString()];
                if (value.ToString() == "")
                {
                    //this.gridView[colNumber, i].Value = null;
                    this.gridView.Rows[i].Cells[colNumber].Text = null;
                }
                else if (ls.Contains(value.ToString()))
                {
                    //this.gridView[colNumber, i].Value = value;
                    this.gridView.Rows[i].Cells[colNumber].Text = value.ToString();
                }
                else
                {
                    //this.gridView[colNumber, i].Value = null;
                    this.gridView.Rows[i].Cells[colNumber].Text = null;
                }
            }
        }

    }
}
